package utils;

/**
 * Created by liushun on 2016/11/18.
 */

import entity.Column;
import entity.DbConnect;
import entity.Table;
import ui.AgCodeMain;

import java.awt.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {

    private static DBUtil db = null;

    private static Connection conn = null;

    private DBUtil() {
        // Exists only to defeat instantiation.
    }

    public static DBUtil getInstance(DbConnect connect) throws Exception {
        if (conn == null) {
            // 加载驱动程序
            switch (connect.getDbType()) {
                case 0:
                    connect.setDriver("com.mysql.jdbc.Driver");
                    break;
                case 1:
                    connect.setDriver("com.mysql.jdbc.Driver");
                    break;
                case 2:
                    connect.setDriver("com.mysql.jdbc.Driver");
                    break;
            }
            AgCodeMain.log("驱动：" + connect.getDriver() + "\n数据库链接：" + connect.getUrl() + "\n账号：" + connect.getUser() + "\n密码：" + connect.getPassword(), Color.black, false);
            // 加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            // 连续数据库
            conn = DriverManager.getConnection(connect.getUrl(), connect.getUser(), connect.getPassword());
            if (!conn.isClosed())
                System.out.println("Succeeded connecting to the Database!");
        } else
            System.out.println("已经连接过了!");
        return db;
    }


    //关闭数据库
    public static void close() {
        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查询所有表信息
    public static List<Table> queryTableList(String dbname) throws SQLException {
        List list = new ArrayList();
        PreparedStatement pst = null;
        pst = conn.prepareStatement("select table_name, table_rows, table_comment from information_schema.tables where table_schema = ?");
        pst.setString(1, dbname);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            list.add(new Table(rs.getString(1), rs.getInt(2), rs.getString(3)));
        }
        rs.close();
        return list;
    }

    //查询表字段信息
    public static List<Column> queryColumnList(String tableName) throws Exception {
        List list = new ArrayList();
        PreparedStatement pst = conn.prepareStatement("SELECT distinct(column_name),column_comment,data_type FROM information_schema.columns WHERE table_name=?");
        pst.setString(1, tableName);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            list.add(new Column(rs.getString("column_name"), rs.getString("data_type"), rs.getString("column_comment")));
        }
        rs.close();
        return list;
    }

}